package com.ujiuye.dao.impl;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ujiuye.dao.UserDao;
import com.ujiuye.entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class UserDaoImpl implements UserDao {
    QueryRunner qr = new QueryRunner(new ComboPooledDataSource());

    @Override
    public User checkUser(String username, String password) {
        String sql = "select * from user where username = ? and password = ?";
        try {
           return  qr.query(sql,new BeanHandler<User>(User.class),username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int totalCount(String name) {
        StringBuilder sql = new StringBuilder("select count(*) from user where 1=1");
        if(name!=null && !"".equals(name)){
            sql.append(" and name like '%"+name+"%'");
        }
        try {
            Long num = qr.query(sql.toString(),new ScalarHandler<Long>());
            return num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<User> selectAllUser(int startIndex, int pageSize, String name) {
        StringBuilder sql = new StringBuilder("select * from user where 1=1");
        if(name!=null && !"".equals(name)){
                sql.append(" and name like '%"+name+"%'");
        }

        sql.append(" limit ?,?");
        try {
            return  qr.query(sql.toString(),new BeanListHandler<User>(User.class),startIndex,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addUser(User u) {
        String sql = "insert into user values(null,?,?,?,?,?,?,?,?,?,null)";
        try {
           return qr.update(sql,u.getName(),u.getPhone(),u.getAge(),
                    u.getSex(),u.getUsername(),u.getPassword(),u.getStatus(),
                    new Date(), u.getRole());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delAll(String uids) {
        String sql = "delete from user where uid in("+uids+")";
        try {
           return qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateUser(User u) {
        String sql = "update user set name = ?,phone = ?,age = ?,sex =?,username = ?,password = ?," +
                "status = ?,createTime = ?,role = ?,picture = ? where uid = ?";
        try {
            return  qr.update(sql,u.getName(),u.getPhone(),u.getAge(),u.getSex(),u.getUsername(),
                    u.getPassword(),u.getStatus(),new Date(),u.getRole(),null,u.getUid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User checkPhone(String tel) {
        String sql = "select * from user where phone = ?";
        try {
           return qr.query(sql,new BeanHandler<User>(User.class),tel);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int registerUser(String name, String password, String phone) {
        String sql = "insert into user(name,phone,sex,password,status,createtime,role) values(?,?,?,?,?,?,?)";
        try {
            return qr.update(sql,name,phone,1,password,1,new Date(),3);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User beforeLogin(String password, String phone) {
        String sql = "select * from user where password = ? and phone = ? ";
        try {
            return qr.query(sql,new BeanHandler<User>(User.class),password,phone);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}